import express from 'express';
import cors from 'cors';
import helmet from 'helmet';
import compression from 'compression';
import cron from 'node-cron';
import { createServer } from 'http';
import { Server } from 'socket.io';
import { createConnection, executeQuery, getOne } from './config/database.js';
import { initSocketService } from './utils/socketService.js';
import { getAttendanceSetting, timeToMinutes, isAfterModificationLimit } from './utils/attendanceSettings.js';
import { getChinaTime, getChinaDateString, getChinaTimeMinutes, formatChinaTime } from './utils/timezone.js';
import authRoutes from './routes/auth.js';
import userRoutes from './routes/users.js';
import classRoutes from './routes/classes.js';
import studentRoutes from './routes/students.js';
import attendanceRoutes from './routes/attendance.js';
import dashboardRoutes from './routes/dashboard.js';
import driverRoutes from './routes/drivers.js';
import settingsRoutes from './routes/settings.js';
import afterSchoolRoutes from './routes/after_school.js';

const app = express();
const PORT = process.env.PORT || 8002;
const httpServer = createServer(app);
const io = new Server(httpServer, {
  cors: {
    
    credentials: true,
    methods: ['GET', 'POST', 'PUT', 'DELETE', 'PATCH']
  }
});

// 安全中间件
app.use(helmet());
app.use(compression());

// CORS配置
app.use(cors({
  
  credentials: true,
  methods: ['GET', 'POST', 'PUT', 'DELETE', 'PATCH'],
  allowedHeaders: ['Content-Type', 'Authorization']
}));

// 解析JSON和URL编码数据
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true, limit: '10mb' }));

// 自动异常处理定时任务
async function autoMarkAbsentAsAbnormal() {
  try {
    const currentDate = getChinaDateString();
    const currentTime = getChinaTimeMinutes();
    
    // 获取入园时间设置
    const arrivalTimeSetting = await getAttendanceSetting('arrival_time');
    const cutoffTime = timeToMinutes(arrivalTimeSetting);
    
    console.log(`🕐 当前中国时间: ${formatChinaTime()}, 当前分钟数: ${currentTime}, 入园截止分钟数: ${cutoffTime}`);
    
    // 只有当前时间超过入园时间时才执行
    if (currentTime >= cutoffTime) {
      const absentStudents = await executeQuery(
        'SELECT id, student_id FROM attendance_records WHERE attendance_date = ? AND arrival_status = ?',
        [currentDate, 'absent']
      );
      
      if (absentStudents.length > 0) {
        console.log(`🔄 自动标记 ${absentStudents.length} 个absent状态学生为异常`);
        
        for (const student of absentStudents) {
          await executeQuery(
            'UPDATE attendance_records SET arrival_status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
            ['abnormal', student.id]
          );
        }
        
        console.log('✅ 自动异常处理完成');
        
        // 异常处理完成后广播状态更新
        broadcastStudentStatusUpdate();
      }
    }
  } catch (error) {
    console.error('❌ 自动异常处理失败:', error);
  }
}

// 自动生成当日考勤记录
async function generateDailyAttendanceRecords() {
  try {
    const today = new Date().toISOString().split('T')[0];
    
    // 检查今日是否已有记录
    const existingRecords = await executeQuery(
      'SELECT COUNT(*) as count FROM attendance_records WHERE attendance_date = ?',
      [today]
    );
    
    if (existingRecords[0].count > 0) {
      console.log(`📅 今日(${today})已存在考勤记录，跳过生成`);
      return;
    }
    
    // 获取所有学生
    const students = await executeQuery('SELECT id FROM students');
    
    if (students.length === 0) {
      console.log('⚠️ 没有找到学生数据，跳过生成考勤记录');
      return;
    }
    
    // 批量插入考勤记录
    const insertPromises = students.map(student => 
      executeQuery(
        `INSERT INTO attendance_records 
         (student_id, attendance_date, arrival_status, departure_status) 
         VALUES (?, ?, 'absent', 'not_left')`,
        [student.id, today]
      )
    );
    
    await Promise.all(insertPromises);
    
    console.log(`✅ 定时任务：成功为${students.length}名学生生成${today}的考勤记录`);
  } catch (error) {
    console.error('❌ 定时生成考勤记录失败:', error);
  }
}

// 定期广播学生状态更新
async function broadcastStudentStatusUpdate() {
  try {
    const { emitStudentStatusUpdate } = await import('./utils/socketService.js');
    
    // 获取当日所有学生的最新状态
    const currentDate = new Date().toISOString().split('T')[0];
    const studentsStatus = await executeQuery(`
      SELECT 
        s.id as student_id,
        s.student_no,
        s.name,
        s.class_id,
        c.name as class_name,
        COALESCE(ar.arrival_status, 'absent') as arrival_status,
        COALESCE(ar.departure_status, 'not_left') as departure_status,
        ar.notes,
        ar.updated_at
      FROM students s
      LEFT JOIN classes c ON s.class_id = c.id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
      ORDER BY c.name, s.name
    `, [currentDate]);
    
    // 广播给所有已认证用户
    emitStudentStatusUpdate({
      type: 'periodic_update',
      timestamp: new Date().toISOString(),
      data: studentsStatus
    });
    
    console.log(`📡 已广播 ${studentsStatus.length} 名学生的状态更新`);
  } catch (error) {
    console.error('❌ 广播学生状态更新失败:', error);
  }
}

// 测试数据库连接
async function testDatabaseConnection() {
  try {
    const connection = await createConnection();
    console.log('✅ 数据库连接成功');
    await connection.end();
  } catch (error) {
    console.error('❌ 数据库连接失败:', error.message);
    process.exit(1);
  }
}

// API路由
app.use('/api/auth', authRoutes);
app.use('/api/users', userRoutes);
app.use('/api/classes', classRoutes);
app.use('/api/students', studentRoutes);
app.use('/api/attendance', attendanceRoutes);
app.use('/api/dashboard', dashboardRoutes);
app.use('/api/drivers', driverRoutes);
app.use('/api/settings', settingsRoutes);
app.use('/api/after-school', afterSchoolRoutes);

// 根路径
app.get('/', (req, res) => {
  res.json({
    message: '🎓 Newton校园考勤系统API',
    version: '1.0.0',
    status: 'running'
  });
});

// 404处理
app.use('*', (req, res) => {
  res.status(404).json({
    success: false,
    message: '请求的资源不存在'
  });
});

// 全局错误处理
app.use((error, req, res, next) => {
  console.error('服务器错误:', error);
  
  res.status(error.status || 500).json({
    success: false,
    message: error.message || '服务器内部错误',
    ...(process.env.NODE_ENV === 'development' && { stack: error.stack })
  });
});

// 初始化WebSocket服务
initSocketService(io);

// WebSocket事件处理
io.on('connection', (socket) => {
  console.log('👤 新客户端连接:', socket.id);
  
  // 用户认证
  socket.on('authenticate', async (token) => {
    try {
      const jwt = await import('jsonwebtoken');
      const JWT_SECRET = 'newton_school_jwt_secret_key_2024';
      
      const decoded = jwt.default.verify(token, JWT_SECRET);
      
      // 验证用户是否存在
      const user = await getOne(
        'SELECT id, username, name, role, phone FROM users WHERE id = ?',
        [decoded.userId]
      );

      if (!user) {
        socket.emit('auth-error', '用户不存在');
        return;
      }

      // 设置socket用户信息
      socket.userId = user.id;
      socket.userRole = user.role;
      socket.userName = user.name;
      
      console.log('用户认证成功:', user.name, user.role);
      socket.join('authenticated');
      socket.emit('auth-success', { user });
    } catch (error) {
      console.error('Token验证错误:', error);
      socket.emit('auth-error', '无效的访问令牌');
    }
  });
  
  // 加入特定角色房间
  socket.on('join-role', (role) => {
    console.log(`用户加入${role}角色房间`);
    socket.join(role);
  });
  
  // 加入特定班级房间
  socket.on('join-class', (classId) => {
    console.log(`用户加入班级房间:${classId}`);
    socket.join(`class-${classId}`);
  });
  
  // 加入课后托班房间
  socket.on('join-after-school', (afterSchoolClassId) => {
    console.log(`用户加入课后托班房间:${afterSchoolClassId}`);
    socket.join(`after-school-${afterSchoolClassId}`);
  });
  
  // 学生状态更新
  socket.on('student-status-update', (data) => {
    console.log('学生状态更新:', data);
    // 广播给所有已认证用户
    io.to('authenticated').emit('student-status-updated', data);
    // 如果有班级ID，也发送到特定班级房间
    if (data.classId) {
      io.to(`class-${data.classId}`).emit('student-status-updated', data);
    }
  });
  
  // 教师仪表盘数据请求
  socket.on('get-teacher-dashboard', async (data) => {
    try {
      console.log('获取教师仪表盘数据:', data);
      const { teacherId, date = new Date().toISOString().split('T')[0] } = data;
      
      // 获取教师负责的班级
      const teacherClasses = await executeQuery(`
        SELECT c.id, c.name, c.grade
        FROM classes c
        INNER JOIN teacher_classes tc ON c.id = tc.class_id
        WHERE tc.teacher_id = ?
      `, [teacherId]);

      if (teacherClasses.length === 0) {
        socket.emit('teacher-dashboard-data', {
          success: true,
          data: {
            date,
            classes: [],
            summary: {
              total_students: 0,
              present_count: 0,
              leave_count: 0,
              abnormal_count: 0,
              attendance_rate: 0
            }
          }
        });
        return;
      }

      const classIds = teacherClasses.map(c => c.id);

      // 获取各班级统计
      const classStats = await executeQuery(`
        SELECT 
          c.id,
          c.name as class_name,
          c.grade,
          COUNT(DISTINCT s.id) as total_students,
          COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) as present_count,
          COUNT(CASE WHEN ar.arrival_status = 'leave' AND ar.attendance_date = ? THEN 1 END) as leave_count,
          COUNT(CASE WHEN ar.arrival_status = 'abnormal' AND ar.attendance_date = ? THEN 1 END) as abnormal_count,
          ROUND(
            COUNT(CASE WHEN ar.arrival_status = 'present' AND ar.attendance_date = ? THEN 1 END) * 100.0 / 
            NULLIF(COUNT(DISTINCT s.id), 0), 2
          ) as attendance_rate
        FROM classes c
        LEFT JOIN students s ON c.id = s.class_id
        LEFT JOIN attendance_records ar ON s.id = ar.student_id
        WHERE c.id IN (${classIds.map(() => '?').join(',')})
        GROUP BY c.id, c.name, c.grade
        ORDER BY c.grade, c.name
      `, [date, date, date, date, ...classIds]);

      // 总体统计
      const totalStats = classStats.reduce((acc, cls) => {
        acc.total_students += cls.total_students;
        acc.present_count += cls.present_count;
        acc.leave_count += cls.leave_count;
        acc.abnormal_count += cls.abnormal_count;
        return acc;
      }, {
        total_students: 0,
        present_count: 0,
        leave_count: 0,
        abnormal_count: 0
      });

      totalStats.attendance_rate = totalStats.total_students > 0 
        ? Math.round((totalStats.present_count / totalStats.total_students) * 100 * 100) / 100 
        : 0;
      
      socket.emit('teacher-dashboard-data', {
        success: true,
        data: {
          date,
          classes: classStats,
          summary: totalStats
        }
      });
    } catch (error) {
      console.error('获取教师仪表盘数据失败:', error);
      socket.emit('teacher-dashboard-data', {
        success: false,
        message: '获取仪表盘数据失败'
      });
    }
  });
  
  // 获取班级考勤数据
  socket.on('get-class-attendance', async (data) => {
    try {
      console.log('获取班级考勤数据:', data);
      const { classId, type, teacherId, date = new Date().toISOString().split('T')[0] } = data;
      
      // 获取教师负责的班级
      const teacherClass = await getOne(`
        SELECT class_id FROM teacher_classes WHERE teacher_id = ?
      `, [teacherId]);
      
      if (!teacherClass) {
        socket.emit('class-attendance-data', {
          success: true,
          data: {
            date,
            students: [],
            statistics: {
              total: 0,
              present: 0,
              leave: 0,
              abnormal: 0,
              not_left: 0,
              left_school: 0,
              class_left: 0,
              class_entered: 0
            }
          }
        });
        return;
      }
      
      const targetClassId = teacherClass.class_id;

      // 获取班级信息
      const classInfo = await getOne(`
        SELECT id, name, grade FROM classes WHERE id = ?
      `, [targetClassId]);

      if (!classInfo) {
        socket.emit('class-attendance-data', {
          success: false,
          message: '班级不存在'
        });
        return;
      }

      // 获取学生及考勤状态
      const students = await executeQuery(`
        SELECT 
          s.id, s.student_no, s.name, s.qr_code,
          ar.id as attendance_id,
          ar.attendance_date,
          ar.arrival_status,
          ar.arrival_timestamp, TIME(ar.arrival_timestamp) as arrival_time,
          ar.departure_status,
          ar.departure_method,
          ar.departure_timestamp, TIME(ar.departure_timestamp) as departure_time,
          ar.notes,
          -- 计算attendance_status字段
          CASE 
            WHEN ar.arrival_status = 'present' AND ar.departure_status = 'class_entered' THEN 'entered_class'
            WHEN ar.arrival_status = 'present' AND ar.departure_status = 'class_left' THEN 'left_class'
            WHEN ar.arrival_status = 'present' AND ar.departure_status = 'left_school' THEN 'left_school'
            WHEN ar.arrival_status = 'present' THEN 'arrived'
            WHEN ar.arrival_status = 'leave' THEN 'on_leave'
            WHEN ar.arrival_status = 'abnormal' THEN 'abnormal'
            ELSE 'not_arrived'
          END as attendance_status,
          -- 计算在园时长
          CASE 
            WHEN ar.arrival_timestamp IS NOT NULL AND ar.departure_timestamp IS NOT NULL 
            THEN TIMESTAMPDIFF(MINUTE, ar.arrival_timestamp, ar.departure_timestamp)
            ELSE NULL 
          END as duration_minutes,
          ar.created_at, ar.updated_at
        FROM students s
        LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
        WHERE s.class_id = ?
        ORDER BY s.student_no
      `, [date, targetClassId]);

      // 统计考勤情况
      const stats = {
        total: students.length,
        present: students.filter(s => s.arrival_status === 'present').length,
        leave: students.filter(s => s.arrival_status === 'leave').length,
        abnormal: students.filter(s => s.arrival_status === 'abnormal').length,
        absent: students.filter(s => !s.arrival_status || s.arrival_status === 'absent').length,
        departure_stats: {
          not_left: students.filter(s => !s.departure_status || s.departure_status === 'not_left').length,
          bus: students.filter(s => s.departure_status === 'bus').length,
          parent_pickup: students.filter(s => s.departure_status === 'parent_pickup').length,
          after_school: students.filter(s => s.departure_status === 'after_school').length,
          special_course: students.filter(s => s.departure_status === 'special_course').length,
          abnormal: students.filter(s => s.departure_status === 'abnormal').length
        }
      };

      stats.attendance_rate = stats.total > 0 ? ((stats.present / stats.total) * 100).toFixed(2) : 0;
      
      socket.emit('class-attendance-data', {
        success: true,
        type,
        data: {
          date,
          class: classInfo,
          students,
          statistics: stats
        }
      });
    } catch (error) {
      console.error('获取班级考勤数据失败:', error);
      socket.emit('class-attendance-data', {
        success: false,
        message: '获取考勤数据失败'
      });
    }
  });
  
  // 更新学生到校状态
  socket.on('update-student-arrival', async (data) => {
    try {
      console.log('更新学生到校状态:', data);
      const { studentId, status, notes, teacherId } = data;
      const date = new Date().toISOString().split('T')[0];
      const timestamp = new Date();
      
      // 获取学生信息
      const studentInfo = await getOne(`
        SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        WHERE s.id = ?
      `, [studentId]);

      if (!studentInfo) {
        socket.emit('student-arrival-updated', {
          success: false,
          message: '学生不存在'
        });
        return;
      }

      // 教师权限检查
      const hasPermission = await getOne(`
        SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
      `, [teacherId, studentInfo.class_id]);

      if (!hasPermission) {
        socket.emit('student-arrival-updated', {
          success: false,
          message: '无权为该学生标记考勤'
        });
        return;
      }

      // 检查今日是否已有考勤记录
      const existingRecord = await getOne(`
        SELECT id FROM attendance_records 
        WHERE student_id = ? AND attendance_date = ?
      `, [studentInfo.id, date]);

      let recordId;
      if (existingRecord) {
        // 更新现有记录
        recordId = existingRecord.id;
        await executeQuery(`
          UPDATE attendance_records 
          SET arrival_status = ?, arrival_timestamp = ?, notes = ?, updated_at = CURRENT_TIMESTAMP
          WHERE id = ?
        `, [status, status === 'present' ? timestamp : null, notes || '', recordId]);
      } else {
        // 创建新记录
        const result = await executeQuery(`
          INSERT INTO attendance_records (student_id, attendance_date, arrival_status, arrival_timestamp, notes)
          VALUES (?, ?, ?, ?, ?)
        `, [studentInfo.id, date, status, status === 'present' ? timestamp : null, notes || '']);
        recordId = result.insertId;
      }
      
      // 广播更新给所有相关用户
      const updateData = {
        studentId: studentInfo.id,
        classId: studentInfo.class_id,
        type: 'arrival_updated',
        status: status,
        timestamp: status === 'present' ? timestamp : null,
        notes: notes || ''
      };
      
      socket.emit('student-arrival-updated', {
        success: true,
        data: {
          student: studentInfo,
          status,
          timestamp: status === 'present' ? timestamp : null,
          notes
        }
      });
      
      // 广播给班级房间的其他用户
      socket.to(`class-${studentInfo.class_id}`).emit('student-status-updated', updateData);
      io.to('authenticated').emit('student-status-updated', updateData);
    } catch (error) {
      console.error('更新学生到校状态失败:', error);
      socket.emit('student-arrival-updated', {
        success: false,
        message: '更新到校状态失败'
      });
    }
  });
  
  // 标记学生离园方式（第一步：只标记方式，不改变状态）
  socket.on('update-student-departure', async (data) => {
    try {
      console.log('标记学生离园方式:', data);
      const { studentId, departureType, notes, afterSchoolClassId, teacherId, userRole } = data;
      const date = getChinaDateString();
      const timestamp = getChinaTime();
      
      // 检查时间限制（修改限制时间后只有管理员可以修改离园方式）
      const isAfterLimit = await isAfterModificationLimit();
      if (isAfterLimit && userRole === 'teacher') {
        const limitTime = await getAttendanceSetting('modification_limit_time');
        socket.emit('student-departure-updated', {
          success: false,
          message: `${limitTime}后，只有管理员可以修改离园方式`
        });
        return;
      }

      // 获取学生信息
      const studentInfo = await getOne(`
        SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        WHERE s.id = ?
      `, [studentId]);

      if (!studentInfo) {
        socket.emit('student-departure-updated', {
          success: false,
          message: '学生不存在'
        });
        return;
      }

      // 教师权限检查
      if (userRole === 'teacher') {
        const hasPermission = await getOne(`
          SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
        `, [teacherId, studentInfo.class_id]);

        if (!hasPermission) {
          socket.emit('student-departure-updated', {
            success: false,
            message: '无权为该学生标记离园'
          });
          return;
        }
      }

      // 检查今日考勤记录
      const attendanceRecord = await getOne(`
        SELECT id, arrival_status FROM attendance_records 
        WHERE student_id = ? AND attendance_date = ?
      `, [studentId, date]);

      if (!attendanceRecord) {
        socket.emit('student-departure-updated', {
          success: false,
          message: '学生今日未入园，无法标记离园'
        });
        return;
      }

      if (attendanceRecord.arrival_status !== 'present') {
        socket.emit('student-departure-updated', {
          success: false,
          message: '学生今日未正常入园，无法标记离园'
        });
        return;
      }

      // 处理离园备注
      let finalNotes = notes || '';
      let busInfo = '';
      
      // 如果是校车离园，获取绑定的校车信息
      if (departureType === 'bus') {
        const assignedBus = await getOne(`
          SELECT u.bus_no, u.name as driver_name
          FROM students s
          INNER JOIN users u ON s.assigned_bus_id = u.id
          WHERE s.id = ? AND u.role = 'driver'
        `, [studentId]);
        
        if (assignedBus) {
          busInfo = `校车: ${assignedBus.bus_no} (${assignedBus.driver_name})`;
          finalNotes = finalNotes ? `${finalNotes} - ${busInfo}` : busInfo;
        } else {
          socket.emit('student-departure-updated', {
            success: false,
            message: '该学生未绑定校车，无法标记校车离园'
          });
          return;
        }
      }

      // 只标记离园方式，不改变离园状态
      // 如果是托班或特色课程，同时记录课后班级ID
      if ((departureType === 'after_school' || departureType === 'special_course') && afterSchoolClassId) {
        // 验证课后班级是否存在
        const afterSchoolClass = await getOne('SELECT id, name FROM after_school_classes WHERE id = ?', [afterSchoolClassId]);
        if (!afterSchoolClass) {
          socket.emit('student-departure-updated', {
            success: false,
            message: '选择的课后班级不存在'
          });
          return;
        }
        
        await executeQuery(`
          UPDATE attendance_records 
          SET departure_method = ?, departure_marked_time = ?,
              after_school_class_id = ?, notes = ?, updated_at = CURRENT_TIMESTAMP
          WHERE id = ?
        `, [departureType, timestamp, afterSchoolClassId, finalNotes, attendanceRecord.id]);
      } else {
        await executeQuery(`
          UPDATE attendance_records 
          SET departure_method = ?, departure_marked_time = ?,
              notes = ?, updated_at = CURRENT_TIMESTAMP
          WHERE id = ?
        `, [departureType, timestamp, finalNotes, attendanceRecord.id]);
      }
      
      // 如果是校车离园，添加到校车离园队列
      if (departureType === 'bus') {
        const assignedDriver = await getOne(`
          SELECT 
            u.id as driver_id, 
            u.bus_no,
            u.name as driver_name
          FROM students s
          INNER JOIN users u ON s.assigned_bus_id = u.id
          WHERE s.id = ? AND u.role = 'driver'
        `, [studentId]);
        
        if (assignedDriver) {
          await executeQuery(`
            INSERT INTO bus_departure_queue 
            (student_id, attendance_record_id, departure_date, assigned_driver_id, notes, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, NOW(), NOW())
            ON DUPLICATE KEY UPDATE 
            assigned_driver_id = VALUES(assigned_driver_id),
            notes = VALUES(notes),
            updated_at = NOW()
          `, [studentId, attendanceRecord.id, date, assignedDriver.driver_id, `${assignedDriver.driver_name}的${assignedDriver.bus_no}校车`]);
        }
      }
      
      const updateData = {
        studentId: studentInfo.id,
        classId: studentInfo.class_id,
        type: 'departure_method_marked',
        method: departureType,
        timestamp: timestamp,
        notes: finalNotes
      };
      
      socket.emit('student-departure-updated', {
        success: true,
        message: '离园方式标记成功',
        data: {
          student: studentInfo,
          departureMethod: departureType,
          timestamp,
          notes: finalNotes
        }
      });
      
      // 广播给班级房间的其他用户
      socket.to(`class-${studentInfo.class_id}`).emit('student-status-updated', updateData);
      io.to('authenticated').emit('student-status-updated', updateData);
    } catch (error) {
      console.error('标记学生离园方式失败:', error);
      socket.emit('student-departure-updated', {
        success: false,
        message: '标记离园方式失败'
      });
    }
  });
  
  // 确认单个学生离班（第二步：确认实际离班）
  socket.on('confirm-student-departure', async (data) => {
    try {
      console.log('确认学生离班:', data);
      const { studentId, teacherId, userRole } = data;
      const date = new Date().toISOString().split('T')[0];
      const timestamp = new Date();

      if (!studentId) {
        socket.emit('student-departure-confirmed', {
          success: false,
          message: '缺少学生ID参数'
        });
        return;
      }

      // 获取学生信息
      const studentInfo = await getOne(`
        SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        WHERE s.id = ?
      `, [studentId]);

      if (!studentInfo) {
        socket.emit('student-departure-confirmed', {
          success: false,
          message: '学生不存在'
        });
        return;
      }

      // 教师权限检查
      if (userRole === 'teacher') {
        const hasPermission = await getOne(`
          SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
        `, [teacherId, studentInfo.class_id]);

        if (!hasPermission) {
          socket.emit('student-departure-confirmed', {
            success: false,
            message: '无权确认该学生离班'
          });
          return;
        }
      }

      // 检查今日考勤记录
      const attendanceRecord = await getOne(`
        SELECT id, arrival_status, departure_method, departure_marked_time, departure_timestamp 
        FROM attendance_records 
        WHERE student_id = ? AND attendance_date = ?
      `, [studentId, date]);

      if (!attendanceRecord) {
        socket.emit('student-departure-confirmed', {
          success: false,
          message: '学生今日未入园，无法确认离班'
        });
        return;
      }

      if (attendanceRecord.arrival_status !== 'present') {
        socket.emit('student-departure-confirmed', {
          success: false,
          message: '学生今日未正常入园，无法确认离班'
        });
        return;
      }

      if (!attendanceRecord.departure_method) {
        socket.emit('student-departure-confirmed', {
          success: false,
          message: '请先标记学生离园方式'
        });
        return;
      }

      if (attendanceRecord.departure_timestamp) {
        socket.emit('student-departure-confirmed', {
          success: false,
          message: '学生已确认离班'
        });
        return;
      }

      // 检查离园时间 - 所有离班操作都需要在离园时间之后
      const departureTime = await getAttendanceSetting('departure_time');
      const currentTime = new Date();
      const [hours, minutes] = departureTime.split(':');
      const departureDateTime = new Date();
      departureDateTime.setHours(parseInt(hours), parseInt(minutes), 0, 0);
      
      if (currentTime < departureDateTime) {
        socket.emit('student-departure-confirmed', {
          success: false,
          message: `离园时间未到，请在${departureTime}后再操作`
        });
        return;
      }

      // 根据离园方式决定最终状态
      let finalStatus;
      if (attendanceRecord.departure_method === 'parent_pickup' || attendanceRecord.departure_method === 'bus') {
        // 家长接领或校车直接离校
        finalStatus = 'left_school';
      } else if (attendanceRecord.departure_method === 'after_school' || attendanceRecord.departure_method === 'special_course') {
        // 托班或特色课程先离班
        finalStatus = 'class_left';
      }

      // 更新离班状态
      await executeQuery(`
        UPDATE attendance_records 
        SET departure_status = ?, departure_timestamp = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `, [finalStatus, timestamp, attendanceRecord.id]);
      
      const updateData = {
        studentId: studentInfo.id,
        classId: studentInfo.class_id,
        type: 'student_departure_confirmed',
        status: finalStatus,
        method: attendanceRecord.departure_method,
        timestamp: timestamp
      };
      
      socket.emit('student-departure-confirmed', {
        success: true,
        message: '学生已确认离班',
        data: {
          student: studentInfo,
          departureStatus: finalStatus,
          departureMethod: attendanceRecord.departure_method,
          timestamp
        }
      });
      
      // 广播给班级房间的其他用户
      socket.to(`class-${studentInfo.class_id}`).emit('student-status-updated', updateData);
      io.to('authenticated').emit('student-status-updated', updateData);
    } catch (error) {
      console.error('确认学生离班失败:', error);
      socket.emit('student-departure-confirmed', {
        success: false,
        message: '确认离班失败'
      });
    }
  });

  // 确认班级学生离班（第二步：确认实际离班）
  socket.on('confirm-class-departure', async (data) => {
    try {
      console.log('确认班级学生离班:', data);
      const { classId, studentIds, teacherId, userRole } = data;
      const date = new Date().toISOString().split('T')[0];
      const timestamp = new Date();
      
      if (!classId || !studentIds || !Array.isArray(studentIds) || studentIds.length === 0) {
        socket.emit('class-departure-confirmed', {
          success: false,
          message: '缺少必要参数'
        });
        return;
      }

      // 获取班级信息
      const classInfo = await getOne(`
        SELECT id, name, grade FROM classes WHERE id = ?
      `, [classId]);

      if (!classInfo) {
        socket.emit('class-departure-confirmed', {
          success: false,
          message: '班级不存在'
        });
        return;
      }

      // 教师权限检查
      if (userRole === 'teacher') {
        const hasPermission = await getOne(`
          SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
        `, [teacherId, classId]);

        if (!hasPermission) {
          socket.emit('class-departure-confirmed', {
            success: false,
            message: '无权确认该班级学生离班'
          });
          return;
        }
      }

      // 批量确认学生离班
      const placeholders = studentIds.map(() => '?').join(',');
      const updateResult = await executeQuery(`
        UPDATE attendance_records ar
        INNER JOIN students s ON ar.student_id = s.id
        SET ar.departure_timestamp = ?, ar.updated_at = CURRENT_TIMESTAMP
        WHERE ar.student_id IN (${placeholders}) 
        AND ar.attendance_date = ?
        AND s.class_id = ?
        AND ar.arrival_status = 'present'
        AND ar.departure_method IS NOT NULL
        AND ar.departure_timestamp IS NULL
      `, [timestamp, ...studentIds, date, classId]);

      // 获取已确认的学生信息
      const confirmedStudents = await executeQuery(`
        SELECT s.id, s.student_no, s.name, ar.departure_method
        FROM students s
        INNER JOIN attendance_records ar ON s.id = ar.student_id
        WHERE s.id IN (${placeholders})
        AND ar.attendance_date = ?
        AND s.class_id = ?
        AND ar.departure_timestamp = ?
      `, [...studentIds, date, classId, timestamp]);
      
      const updateData = {
        classId: classId,
        type: 'class_departure_confirmed',
        students: confirmedStudents,
        timestamp: timestamp
      };
      
      socket.emit('class-departure-confirmed', {
        success: true,
        message: `成功确认 ${confirmedStudents.length} 名学生离班`,
        data: {
          class: classInfo,
          confirmedStudents,
          timestamp
        }
      });
      
      // 广播给班级房间的其他用户
      socket.to(`class-${classId}`).emit('student-status-updated', updateData);
      io.to('authenticated').emit('student-status-updated', updateData);
    } catch (error) {
      console.error('确认班级学生离班失败:', error);
      socket.emit('class-departure-confirmed', {
        success: false,
        message: '确认离班失败'
      });
    }
  });
  
  // 更新学生备注（独立功能，不与状态绑定）
  socket.on('update-student-notes', async (data) => {
    try {
      console.log('更新学生备注:', data);
      const { studentId, notes, teacherId, userRole } = data;
      const date = new Date().toISOString().split('T')[0];
      
      // 获取学生信息
      const studentInfo = await getOne(`
        SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        WHERE s.id = ?
      `, [studentId]);

      if (!studentInfo) {
        socket.emit('student-notes-updated', {
          success: false,
          message: '学生不存在'
        });
        return;
      }

      // 教师权限检查
      if (userRole === 'teacher') {
        const hasPermission = await getOne(`
          SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
        `, [teacherId, studentInfo.class_id]);

        if (!hasPermission) {
          socket.emit('student-notes-updated', {
            success: false,
            message: '无权为该学生添加备注'
          });
          return;
        }
      }

      // 检查今日考勤记录是否存在，如果不存在则创建
      let attendanceRecord = await getOne(`
        SELECT id FROM attendance_records 
        WHERE student_id = ? AND attendance_date = ?
      `, [studentId, date]);

      if (!attendanceRecord) {
        // 创建考勤记录
        const result = await executeQuery(`
          INSERT INTO attendance_records (student_id, attendance_date, notes, created_at, updated_at)
          VALUES (?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        `, [studentId, date, notes || '']);
        
        attendanceRecord = { id: result.insertId };
      } else {
        // 更新现有记录的备注
        await executeQuery(`
          UPDATE attendance_records 
          SET notes = ?, updated_at = CURRENT_TIMESTAMP
          WHERE id = ?
        `, [notes || '', attendanceRecord.id]);
      }
      
      const updateData = {
        studentId: studentInfo.id,
        classId: studentInfo.class_id,
        type: 'notes_updated',
        notes: notes || '',
        timestamp: new Date()
      };
      
      socket.emit('student-notes-updated', {
        success: true,
        data: {
          student: studentInfo,
          notes: notes || ''
        }
      });
      
      // 广播给班级房间的其他用户
      socket.to(`class-${studentInfo.class_id}`).emit('student-status-updated', updateData);
      io.to('authenticated').emit('student-status-updated', updateData);
    } catch (error) {
      console.error('更新学生备注失败:', error);
      socket.emit('student-notes-updated', {
        success: false,
        message: '更新备注失败'
      });
    }
  });
  
  // QR码扫描更新到校状态
  socket.on('qr-scan-arrival', async (data) => {
    try {
      console.log('QR码扫描更新到校:', data);
      const { qrCode, teacherId } = data;
      const date = new Date().toISOString().split('T')[0];
      const timestamp = new Date();
      
      // 通过学号查找学生（只验证学号信息）
      const studentInfo = await getOne(`
        SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        WHERE s.student_no = ?
      `, [qrCode]);

      if (!studentInfo) {
        socket.emit('qr-arrival-updated', {
          success: false,
          message: '学生不存在或QR码无效'
        });
        return;
      }

      // 教师权限检查
      const hasPermission = await getOne(`
        SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
      `, [teacherId, studentInfo.class_id]);

      if (!hasPermission) {
        socket.emit('qr-arrival-updated', {
          success: false,
          message: '无权为该学生标记考勤'
        });
        return;
      }

      // 检查今日是否已有考勤记录
      const existingRecord = await getOne(`
        SELECT id FROM attendance_records 
        WHERE student_id = ? AND attendance_date = ?
      `, [studentInfo.id, date]);

      let recordId;
      if (existingRecord) {
        // 更新现有记录
        recordId = existingRecord.id;
        await executeQuery(`
          UPDATE attendance_records 
          SET arrival_status = 'present', arrival_timestamp = ?, updated_at = CURRENT_TIMESTAMP
          WHERE id = ?
        `, [timestamp, recordId]);
      } else {
        // 创建新记录
        const result = await executeQuery(`
          INSERT INTO attendance_records (student_id, attendance_date, arrival_status, arrival_timestamp)
           VALUES (?, ?, 'present', ?)
        `, [studentInfo.id, date, timestamp]);
        recordId = result.insertId;
      }
      
      const updateData = {
        studentId: studentInfo.id,
        classId: studentInfo.class_id,
        type: 'arrival_updated',
        status: 'present',
        timestamp: timestamp
      };
      
      socket.emit('qr-arrival-updated', {
        success: true,
        data: {
          student: studentInfo,
          status: 'present',
          timestamp: timestamp
        }
      });
      
      // 广播给相关房间
      socket.to(`class-${studentInfo.class_id}`).emit('student-status-updated', updateData);
      io.to('authenticated').emit('student-status-updated', updateData);
    } catch (error) {
      console.error('QR码扫描更新失败:', error);
      socket.emit('qr-arrival-updated', {
        success: false,
        message: 'QR码扫描失败'
      });
    }
  });
  
  // 导入学生数据
  socket.on('import-students', async (data) => {
    try {
      console.log('WebSocket导入学生数据:', data);
      const { students, userId, userRole } = data;
      
      // 权限检查
      if (userRole !== 'admin') {
        socket.emit('students-import-result', {
          success: false,
          message: '权限不足，只有管理员可以导入学生数据'
        });
        return;
      }
      
      if (!students || !Array.isArray(students) || students.length === 0) {
        socket.emit('students-import-result', {
          success: false,
          message: '无效的学生数据'
        });
        return;
      }
      
      let imported = 0;
      let errors = [];
      
      // 处理每个学生记录
      for (const student of students) {
        try {
          // 验证必要字段
          if (!student.student_no || !student.name || !student.class_id) {
            errors.push({
              student: student.name || student.student_no || '未知',
              error: '缺少必要字段(学号、姓名或班级ID)'
            });
            continue;
          }
          
          // 验证司机ID（如果提供）
          if (student.driver_id) {
            const driverExists = await getOne(
              'SELECT id FROM users WHERE id = ? AND role = "driver"',
              [student.driver_id]
            );
            if (!driverExists) {
              errors.push({
                student: student.name || student.student_no || '未知',
                error: `司机ID ${student.driver_id} 不存在或不是司机角色`
              });
              continue;
            }
          }
          
          // 检查学号是否已存在
          const existingStudent = await getOne(
            'SELECT id FROM students WHERE student_no = ?',
            [student.student_no]
          );
          
          if (existingStudent) {
            // 更新现有学生
            await executeQuery(
              `UPDATE students SET 
                name = ?, 
                class_id = ?, 
                assigned_bus_id = ?,
                updated_at = CURRENT_TIMESTAMP 
              WHERE id = ?`,
              [
                student.name,
                student.class_id,
                student.driver_id || null,
                existingStudent.id
              ]
            );
          } else {
            // 插入新学生（不再生成二维码）
            await executeQuery(
              `INSERT INTO students 
                (student_no, name, class_id, assigned_bus_id) 
              VALUES (?, ?, ?, ?)`,
              [
                student.student_no,
                student.name,
                student.class_id,
                student.driver_id || null
              ]
            );
          }
          
          imported++;
        } catch (studentError) {
          console.error('导入单个学生错误:', studentError);
          errors.push({
            student: student.name || student.student_no || '未知',
            error: studentError.message
          });
        }
      }
      
      socket.emit('students-import-result', {
        success: true,
        imported,
        errors,
        total: students.length,
        message: `成功导入${imported}个学生${errors.length > 0 ? `，${errors.length}条记录失败` : ''}`
      });
      
      // 广播学生数据更新
      io.to('authenticated').emit('student-status-updated', {
        type: 'students_imported',
        timestamp: new Date().toISOString()
      });
      
    } catch (error) {
      console.error('导入学生数据失败:', error);
      socket.emit('students-import-result', {
        success: false,
        message: '导入学生数据失败: ' + error.message
      });
    }
  });
  
  // 获取学生管理数据
  socket.on('get-students-admin', async (data) => {
    try {
      console.log('获取学生管理数据:', data);
      const { classId, page = 1, limit = 50, search } = data;
      
      let sql = `
        SELECT 
          s.id,
          s.student_no,
          s.name,
          s.class_id,
          s.qr_code,
          s.assigned_bus_id,
          c.name as class_name,
          c.grade,
          u.name as driver_name,
          u.bus_no as driver_bus_no,
          ar.arrival_status,
          ar.arrival_timestamp as arrival_time,
          ar.departure_status,
          ar.departure_timestamp as departure_time
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        LEFT JOIN users u ON s.assigned_bus_id = u.id AND u.role = 'driver'
        LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = CURDATE()
      `;
      
      let params = [];
      let whereConditions = [];
      
      // 班级筛选
      if (classId) {
        whereConditions.push('s.class_id = ?');
        params.push(classId);
      }
      
      // 搜索条件
      if (search) {
        whereConditions.push('(s.name LIKE ? OR s.student_no LIKE ?)');
        params.push(`%${search}%`, `%${search}%`);
      }
      
      if (whereConditions.length > 0) {
        sql += ' WHERE ' + whereConditions.join(' AND ');
      }
      
      sql += ' ORDER BY c.grade, c.name, s.name';
      
      const students = await executeQuery(sql, params);
      
      socket.emit('students-admin-data', {
        success: true,
        students: students,
        total: students.length
      });
    } catch (error) {
      console.error('获取学生管理数据失败:', error);
      socket.emit('students-admin-data', {
        success: false,
        message: '获取学生数据失败'
      });
    }
  });
  
  // 获取班级列表
  socket.on('get-classes', async () => {
    try {
      console.log('获取班级列表');
      const classes = await executeQuery(`
        SELECT 
          id,
          name,
          grade,
          (SELECT COUNT(*) FROM students WHERE class_id = classes.id) as student_count
        FROM classes 
        ORDER BY grade, name
      `);
      
      socket.emit('classes-data', {
        success: true,
        classes: classes
      });
    } catch (error) {
      console.error('获取班级列表失败:', error);
      socket.emit('classes-data', {
        success: false,
        message: '获取班级列表失败'
      });
    }
  });
  
  // 获取司机列表
  socket.on('get-drivers', async () => {
    try {
      console.log('获取司机列表');
      const drivers = await executeQuery(`
        SELECT 
          u.id, u.name, u.phone,
          u.license_no, u.bus_no
        FROM users u
        WHERE u.role = 'driver'
        ORDER BY u.name
      `);
      
      socket.emit('drivers-data', {
        success: true,
        drivers: drivers
      });
    } catch (error) {
      console.error('获取司机列表失败:', error);
      socket.emit('drivers-data', {
        success: false,
        message: '获取司机列表失败'
      });
    }
  });
  
  // 创建学生
  socket.on('create-student', async (studentData) => {
    try {
      console.log('创建学生:', studentData);
      const { student_no, name, class_id, assigned_bus_id } = studentData;
      
      // 验证必要字段
      if (!student_no || !name || !class_id) {
        socket.emit('student-created', {
          success: false,
          message: '学号、姓名和班级为必填项'
        });
        return;
      }
      
      // 检查学号是否已存在
      const existingStudent = await getOne('SELECT id FROM students WHERE student_no = ?', [student_no]);
      if (existingStudent) {
        socket.emit('student-created', {
          success: false,
          message: '学号已存在'
        });
        return;
      }
      
      // 检查班级是否存在
      const classExists = await getOne('SELECT id FROM classes WHERE id = ?', [class_id]);
      if (!classExists) {
        socket.emit('student-created', {
          success: false,
          message: '班级不存在'
        });
        return;
      }
      
      // 如果提供了校车司机ID，检查司机是否存在
      if (assigned_bus_id) {
        const driverExists = await getOne('SELECT id FROM users WHERE id = ? AND role = "driver"', [assigned_bus_id]);
        if (!driverExists) {
          socket.emit('student-created', {
            success: false,
            message: '校车司机不存在'
          });
          return;
        }
      }
      
      // 生成二维码（使用学号）
      // 创建学生（不再生成二维码）
      const result = await executeQuery(`
        INSERT INTO students (student_no, name, class_id, assigned_bus_id) 
        VALUES (?, ?, ?, ?)
      `, [student_no, name, class_id, assigned_bus_id || null]);
      
      socket.emit('student-created', {
        success: true,
        message: '学生添加成功',
        data: {
          id: result.insertId,
          student_no,
          name,
          class_id,
          assigned_bus_id: assigned_bus_id || null
        }
      });
      
    } catch (error) {
      console.error('创建学生错误:', error);
      socket.emit('student-created', {
        success: false,
        message: '服务器错误'
      });
    }
  });
  
  // 更新学生信息
  socket.on('update-student', async (data) => {
    try {
      console.log('更新学生信息:', data);
      const { studentId, studentData } = data;
      const { name, assigned_bus_id } = studentData;
      
      // 检查学生是否存在
      const student = await getOne('SELECT id, class_id FROM students WHERE id = ?', [studentId]);
      if (!student) {
        socket.emit('student-updated', {
          success: false,
          message: '学生不存在'
        });
        return;
      }
      
      // 如果提供了校车司机ID，检查司机是否存在
      if (assigned_bus_id !== undefined && assigned_bus_id !== null && assigned_bus_id !== '') {
        const driverExists = await getOne('SELECT id FROM users WHERE id = ? AND role = "driver"', [assigned_bus_id]);
        if (!driverExists) {
          socket.emit('student-updated', {
            success: false,
            message: '校车司机不存在'
          });
          return;
        }
      }
      
      // 构建更新字段
      const updateFields = [];
      const updateValues = [];
      
      if (name) {
        updateFields.push('name = ?');
        updateValues.push(name);
      }
      
      if (assigned_bus_id !== undefined) {
        updateFields.push('assigned_bus_id = ?');
        updateValues.push(assigned_bus_id || null);
      }
      
      if (updateFields.length === 0) {
        socket.emit('student-updated', {
          success: false,
          message: '没有提供要更新的字段'
        });
        return;
      }
      
      updateFields.push('updated_at = CURRENT_TIMESTAMP');
      updateValues.push(studentId);
      
      await executeQuery(`
        UPDATE students 
        SET ${updateFields.join(', ')} 
        WHERE id = ?
      `, updateValues);
      
      socket.emit('student-updated', {
        success: true,
        message: '学生信息更新成功'
      });
      
    } catch (error) {
      console.error('更新学生错误:', error);
      socket.emit('student-updated', {
        success: false,
        message: '服务器错误'
      });
    }
  });
  
  // 管理员更新学生状态（主要用于修改离园方式）
  socket.on('update-student-status', async (data) => {
    try {
      console.log('管理员更新学生状态:', data);
      const { studentId, statusData } = data;
      const { arrival_status, departure_status, bus_no, notes } = statusData;
      const date = getChinaDateString();
      const timestamp = getChinaTime();
      
      // 获取学生信息
      const studentInfo = await getOne(`
        SELECT s.id, s.student_no, s.name, s.class_id, c.name as class_name
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        WHERE s.id = ?
      `, [studentId]);

      if (!studentInfo) {
        socket.emit('student-status-updated', {
          success: false,
          message: '学生不存在'
        });
        return;
      }

      // 检查今日考勤记录
      let attendanceRecord = await getOne(`
        SELECT id, arrival_status, departure_status FROM attendance_records 
        WHERE student_id = ? AND attendance_date = ?
      `, [studentId, date]);

      if (!attendanceRecord) {
        // 如果没有考勤记录，创建一条
        const result = await executeQuery(`
          INSERT INTO attendance_records (student_id, attendance_date, arrival_status, departure_status, created_at, updated_at)
          VALUES (?, ?, 'absent', 'not_left', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
        `, [studentId, date]);
        
        attendanceRecord = {
          id: result.insertId,
          arrival_status: 'absent',
          departure_status: 'not_left'
        };
      }

      // 构建更新字段
      const updateFields = [];
      const updateValues = [];
      
      if (arrival_status !== undefined && arrival_status !== null) {
        updateFields.push('arrival_status = ?');
        updateValues.push(arrival_status);
      }
      
      if (departure_status !== undefined && departure_status !== null) {
        updateFields.push('departure_method = ?');
        updateValues.push(departure_status);
        
        // 如果是校车离园，处理校车信息
        if (departure_status === 'bus' && bus_no) {
          const finalNotes = notes ? `${notes} (校车: ${bus_no})` : `校车: ${bus_no}`;
          updateFields.push('notes = ?');
          updateValues.push(finalNotes);
        } else if (notes !== undefined) {
          updateFields.push('notes = ?');
          updateValues.push(notes);
        }
      } else if (notes !== undefined) {
        updateFields.push('notes = ?');
        updateValues.push(notes);
      }
      
      if (updateFields.length > 0) {
        updateFields.push('updated_at = CURRENT_TIMESTAMP');
        updateValues.push(attendanceRecord.id);
        
        await executeQuery(`
          UPDATE attendance_records 
          SET ${updateFields.join(', ')} 
          WHERE id = ?
        `, updateValues);
      }
      
      const updateData = {
        studentId: studentInfo.id,
        classId: studentInfo.class_id,
        type: 'admin_status_update',
        timestamp: timestamp
      };
      
      socket.emit('student-status-updated', {
        success: true,
        message: '学生状态更新成功',
        data: {
          student: studentInfo,
          statusData,
          timestamp
        }
      });
      
      // 广播给班级房间的其他用户
      socket.to(`class-${studentInfo.class_id}`).emit('student-status-updated', updateData);
      io.to('authenticated').emit('student-status-updated', updateData);
      
    } catch (error) {
      console.error('更新学生状态失败:', error);
      socket.emit('student-status-updated', {
        success: false,
        message: '更新学生状态失败'
      });
    }
  });

  // 删除学生
  socket.on('delete-student', async (data) => {
    try {
      console.log('删除学生:', data);
      const { studentId } = data;
      
      // 检查学生是否存在
      const student = await getOne('SELECT id, class_id FROM students WHERE id = ?', [studentId]);
      if (!student) {
        socket.emit('student-deleted', {
          success: false,
          message: '学生不存在'
        });
        return;
      }
      
      await executeQuery('DELETE FROM students WHERE id = ?', [studentId]);
      
      socket.emit('student-deleted', {
        success: true,
        message: '学生删除成功'
      });
      
    } catch (error) {
      console.error('删除学生错误:', error);
      socket.emit('student-deleted', {
        success: false,
        message: '服务器错误'
      });
    }
  });
  
  // 获取学生考勤数据
  socket.on('get-students-attendance', async (data) => {
    try {
      console.log('获取学生考勤数据:', data);
      const { date = new Date().toISOString().split('T')[0] } = data;
      
      const students = await executeQuery(`
        SELECT 
           s.id,
           s.student_no,
           s.name,
           s.class_id,
           c.name as class_name,
           c.grade,
           ar.arrival_status,
           ar.arrival_timestamp as arrival_time,
           ar.departure_status,
           ar.departure_timestamp as departure_time
         FROM students s
         INNER JOIN classes c ON s.class_id = c.id
         LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
        ORDER BY c.grade, c.name, s.name
      `, [date]);
      
      socket.emit('students-attendance-data', {
        success: true,
        data: students
      });
    } catch (error) {
      console.error('获取学生考勤数据失败:', error);
      socket.emit('students-attendance-data', {
        success: false,
        message: '获取学生考勤数据失败'
      });
    }
  });

  // ==================== 课后教师相关WebSocket事件处理器 ====================
  
  // 获取课后托班班级列表
  socket.on('get-after-school-classes', async (data) => {
    try {
      console.log('获取课后托班班级列表:', data);
      const { teacherId, userRole } = data;
      
      let sql = `
        SELECT 
          ac.id, ac.name, ac.description, 
          ac.teacher_id, u.name as teacher_name,
          ac.created_at, ac.updated_at
        FROM after_school_classes ac
        JOIN users u ON ac.teacher_id = u.id
      `;
      
      let params = [];
      
      // 如果是课后老师，只能看到自己负责的班级
      if (userRole === 'after_school_teacher') {
        sql += ' WHERE ac.teacher_id = ?';
        params.push(teacherId);
      }
      
      sql += ' ORDER BY ac.name';
      
      const classes = await executeQuery(sql, params);
      
      socket.emit('after-school-classes-data', {
        success: true,
        data: {
          classes
        }
      });
    } catch (error) {
      console.error('获取课后托班班级列表失败:', error);
      socket.emit('after-school-classes-data', {
        success: false,
        message: '获取课后托班班级列表失败'
      });
    }
  });
  
  // 获取课后托班当日班级队列学生
  socket.on('get-after-school-queue-students', async (data) => {
    try {
      console.log('获取课后托班当日班级队列学生:', data);
      const { teacherId, userRole, date } = data;
      
      // 获取课后老师负责的托班班级ID
      let afterSchoolClassIds = [];
      if (userRole === 'after_school_teacher') {
        const classes = await executeQuery(
          'SELECT id FROM after_school_classes WHERE teacher_id = ?',
          [teacherId]
        );
        afterSchoolClassIds = classes.map(c => c.id);
      }
      
      if (afterSchoolClassIds.length === 0 && userRole === 'after_school_teacher') {
        socket.emit('after-school-queue-students-data', {
          success: true,
          students: [],
          total: 0
        });
        return;
      }
      
      // 查询当日标记为托班或特色课程的学生
      let sql = `
        SELECT 
          ar.id as attendance_record_id,
          ar.student_id,
          s.name as student_name,
          s.student_no,
          s.class_id,
          c.name as class_name,
          ar.departure_method,
          ar.departure_status,
          ar.after_school_class_id,
          ar.after_school_check_in_time,
          ar.after_school_check_out_time,
          ar.notes,
          asc_table.name as after_school_class_name
        FROM attendance_records ar
        JOIN students s ON ar.student_id = s.id
        JOIN classes c ON s.class_id = c.id
        LEFT JOIN after_school_classes asc_table ON ar.after_school_class_id = asc_table.id
        WHERE DATE(ar.attendance_date) = ?
          AND ar.departure_method IN ('after_school', 'special_class')
      `;
      
      let params = [date];
      
      // 如果是课后老师，只显示分配给自己的托班学生
      if (userRole === 'after_school_teacher' && afterSchoolClassIds.length > 0) {
        sql += ` AND ar.after_school_class_id IN (${afterSchoolClassIds.map(() => '?').join(',')})`;
        params.push(...afterSchoolClassIds);
      }
      
      sql += ' ORDER BY ar.after_school_check_in_time DESC, s.name';
      
      const students = await executeQuery(sql, params);
      
      // 为每个学生添加签到状态和考勤状态
      const studentsWithStatus = students.map(student => ({
        ...student,
        check_in_status: student.after_school_check_in_time ? 
          (student.after_school_check_out_time ? 'checked_out' : 'checked_in') : 
          'not_checked_in',
        // 添加attendance_status字段，基于departure_status映射
        attendance_status: (() => {
          switch(student.departure_status) {
            case 'class_entered': return 'class_entered';
            case 'class_left': return 'left_class';
            case 'left_school': return 'left_school';
            case 'not_left': return 'present';
            default: return 'present';
          }
        })()
      }));
      
      socket.emit('after-school-queue-students-data', {
        success: true,
        students: studentsWithStatus,
        total: studentsWithStatus.length
      });
      
    } catch (error) {
      console.error('获取课后托班当日班级队列学生失败:', error);
      socket.emit('after-school-queue-students-data', {
        success: false,
        message: '获取课后托班当日班级队列学生失败'
      });
    }
  });
  
  // 获取课后托班仪表盘数据

  
  // 学生进入托班
  socket.on('after-school-check-in', async (data) => {
    try {
      console.log('学生进入托班:', data);
      const { student_id, attendance_record_id, after_school_class_id, teacherId, userRole, notes } = data;
      
      // 验证学生是否存在
      const student = await getOne('SELECT id, name, class_id FROM students WHERE id = ?', [student_id]);
      if (!student) {
        socket.emit('after-school-check-in-result', {
          success: false,
          message: '学生不存在'
        });
        return;
      }
      
      // 验证考勤记录是否存在
      const record = await getOne('SELECT id, departure_status, after_school_check_out_time FROM attendance_records WHERE id = ?', [attendance_record_id]);
      if (!record) {
        socket.emit('after-school-check-in-result', {
          success: false,
          message: '考勤记录不存在'
        });
        return;
      }
      
      // 验证托班班级是否存在
      const afterSchoolClass = await getOne('SELECT id, teacher_id FROM after_school_classes WHERE id = ?', [after_school_class_id]);
      if (!afterSchoolClass) {
        socket.emit('after-school-check-in-result', {
          success: false,
          message: '托班班级不存在'
        });
        return;
      }
      
      // 验证当前用户是否是该托班的负责老师（管理员除外）
      if (userRole !== 'admin' && afterSchoolClass.teacher_id !== teacherId) {
        socket.emit('after-school-check-in-result', {
          success: false,
          message: '权限不足，只能为自己负责的托班签到学生'
        });
        return;
      }
      
      // 检查学生是否已经离班
      if (record.after_school_check_out_time) {
        socket.emit('after-school-check-in-result', {
          success: false,
          message: '学生已经离班，不能再次签到'
        });
        return;
      }
      
      // 更新考勤记录
      await executeQuery(
        `UPDATE attendance_records 
         SET departure_status = "class_left", 
             after_school_class_id = ?, 
             after_school_check_in_time = NOW(), 
             after_school_check_in_teacher_id = ?, 
             notes = ? 
         WHERE id = ?`,
        [after_school_class_id, teacherId, notes || null, attendance_record_id]
      );
      
      socket.emit('after-school-check-in-result', {
        success: true,
        message: `学生 ${student.name} 已成功进入托班`,
        data: {
          student_id,
          student_name: student.name,
          after_school_class_id
        }
      });
      
      // 广播更新
      const updateData = {
        studentId: student_id,
        classId: student.class_id,
        updateType: 'after_school_check_in',
        status: 'class_left',
        timestamp: new Date(),
        notes: notes || null
      };
      
      io.to('authenticated').emit('student-status-updated', updateData);
      io.to(`after-school-${after_school_class_id}`).emit('after-school-updated', {
        studentId: student_id,
        studentName: student.name,
        attendanceRecordId: attendance_record_id,
        afterSchoolClassId: after_school_class_id,
        action: 'check_in',
        timestamp: new Date(),
        notes: notes || null
      });
    } catch (error) {
      console.error('学生进入托班失败:', error);
      socket.emit('after-school-check-in-result', {
        success: false,
        message: '服务器错误'
      });
    }
  });
  
  // 扫码进入托班 - 简化版本
  socket.on('after-school-scan-check-in', async (data) => {
    try {
      console.log('扫码进入托班:', data);
      const { qrCode, teacherId, userRole, notes } = data;
      
      const today = new Date().toISOString().split('T')[0];
      
      // 先查找学生和考勤记录
      const studentRecord = await getOne(`
        SELECT s.id, s.name, s.student_no, ar.id as attendance_id, ar.departure_status, ar.arrival_status
        FROM students s
        LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
        WHERE s.student_no = ?
      `, [today, qrCode]);
      
      console.log('查找到的学生记录:', studentRecord);
      
      if (!studentRecord) {
        socket.emit('after-school-scan-check-in-result', {
          success: false,
          message: `学号 ${qrCode} 不存在`
        });
        return;
      }
      
      if (!studentRecord.attendance_id) {
        socket.emit('after-school-scan-check-in-result', {
          success: false,
          message: `学生 ${studentRecord.name} 今日未入园，无法进班`
        });
        return;
      }
      
      if (studentRecord.arrival_status !== 'present') {
        socket.emit('after-school-scan-check-in-result', {
          success: false,
          message: `学生 ${studentRecord.name} 今日未正常入园，无法进班`
        });
        return;
      }
      
      // 更新考勤记录状态为已进班
      const result = await executeQuery(
         `UPDATE attendance_records 
          SET departure_status = 'class_entered', updated_at = CURRENT_TIMESTAMP
          WHERE id = ?`,
         [studentRecord.attendance_id]
       );
      
      console.log('更新结果:', result);
      
      socket.emit('after-school-scan-check-in-result', {
        success: true,
        message: `学生 ${studentRecord.name} 扫码进班成功`,
        data: {
          student: {
            id: studentRecord.id,
            name: studentRecord.name,
            student_no: studentRecord.student_no
          }
        }
      });
      
      // 获取更新后的学生完整信息
      const updatedStudent = await executeQuery(
        `SELECT s.id, s.name, s.student_no, s.class_id,
                ar.id as attendance_id, ar.arrival_status, ar.departure_status, ar.departure_method,
                  CASE 
                  WHEN ar.arrival_status = 'present' AND ar.departure_status = 'not_left' THEN 'present'
                  WHEN ar.arrival_status = 'present' AND ar.departure_status = 'class_left' THEN 'left_class'
                  WHEN ar.arrival_status = 'present' AND ar.departure_status = 'class_entered' THEN 'class_entered'
                  WHEN ar.arrival_status = 'present' AND ar.departure_status = 'left_school' THEN 'departed'
                  WHEN ar.arrival_status = 'leave' THEN 'leave'
                  ELSE 'abnormal'
                END as attendance_status
         FROM students s
         LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = CURDATE()
         WHERE s.id = ?`,
        [studentRecord.id]
      );
      
      // 广播更新
      io.to('authenticated').emit('student-status-updated', {
        updateType: 'after_school_scan_check_in',
        studentId: studentRecord.id,
        student: updatedStudent[0] || {},
        status: 'class_entered',
        timestamp: new Date()
      });
      
    } catch (error) {
      console.error('扫码进入托班失败:', error);
      socket.emit('after-school-scan-check-in-result', {
        success: false,
        message: '服务器错误'
      });
    }
  });
  
  // 学生离开托班
  socket.on('after-school-check-out', async (data) => {
    try {
      console.log('学生离开托班:', data);
      const { student_id, attendance_record_id, teacherId, userRole, notes } = data;
      
      // 验证学生是否存在
      const student = await getOne('SELECT id, name, class_id FROM students WHERE id = ?', [student_id]);
      if (!student) {
        socket.emit('after-school-check-out-result', {
          success: false,
          message: '学生不存在'
        });
        return;
      }
      
      // 验证考勤记录是否存在
      const record = await getOne('SELECT id, after_school_class_id, after_school_check_out_time FROM attendance_records WHERE id = ?', [attendance_record_id]);
      if (!record) {
        socket.emit('after-school-check-out-result', {
          success: false,
          message: '考勤记录不存在'
        });
        return;
      }
      
      // 验证托班班级权限
      if (record.after_school_class_id) {
        const afterSchoolClass = await getOne('SELECT teacher_id FROM after_school_classes WHERE id = ?', [record.after_school_class_id]);
        if (afterSchoolClass && userRole !== 'admin' && afterSchoolClass.teacher_id !== teacherId) {
          socket.emit('after-school-check-out-result', {
            success: false,
            message: '权限不足，只能为自己负责的托班签退学生'
          });
          return;
        }
      }
      
      // 如果学生已经离班，则不能再次签退
      if (record.after_school_check_out_time) {
        socket.emit('after-school-check-out-result', {
          success: false,
          message: '学生已经离班'
        });
        return;
      }
      
      // 更新考勤记录 - 托班教师点击离班后直接进入离校状态
      await executeQuery(`
        UPDATE attendance_records 
        SET departure_status = "left_school", after_school_check_out_time = NOW(), departure_timestamp = NOW() 
        WHERE id = ?
      `, [attendance_record_id]);
      
      socket.emit('after-school-check-out-result', {
        success: true,
        message: `学生 ${student.name} 已成功离开托班`,
        data: {
          student_id,
          student_name: student.name
        }
      });
      
      // 广播更新
      const updateData = {
        studentId: student_id,
        classId: student.class_id,
        updateType: 'after_school_check_out',
        status: 'left_school',
        timestamp: new Date(),
        notes: notes || null
      };
      
      io.to('authenticated').emit('student-status-updated', updateData);
      io.to(`after-school-${record.after_school_class_id}`).emit('after-school-updated', {
        studentId: student_id,
        studentName: student.name,
        attendanceRecordId: attendance_record_id,
        afterSchoolClassId: record.after_school_class_id,
        action: 'check_out',
        timestamp: new Date(),
        notes: notes || null
      });
    } catch (error) {
      console.error('学生离开托班失败:', error);
      socket.emit('after-school-check-out-result', {
        success: false,
        message: '服务器错误'
      });
    }
  });
  
  // 加入课后托班房间
  socket.on('join-after-school-room', (data) => {
    const { classId } = data;
    console.log(`用户加入课后托班房间: after-school-${classId}`);
    socket.join(`after-school-${classId}`);
  });
  
  // 司机获取离园学生名单
  socket.on('get-departure-students', async (data) => {
    try {
      console.log('司机获取离园学生名单:', data);
      const { date = new Date().toISOString().split('T')[0] } = data;
      const userId = socket.userId;
      
      // 验证用户是否为司机
      const driver = await getOne('SELECT id, bus_no FROM users WHERE id = ? AND role = "driver"', [userId]);
      if (!driver) {
        socket.emit('departure-students-data', {
          success: false,
          message: '权限不足，只有司机可以查看离园名单'
        });
        return;
      }
      
      // 获取当日标记为校车离园方式的学生名单
      const students = await executeQuery(`
        SELECT 
          s.id, s.student_no, s.name as student_name,
          c.name as class_name, c.grade,
          ar.departure_status, ar.departure_timestamp as departure_time,
          ar.departure_method, ar.notes as departure_notes,
          ar.bus_driver_id,
          ar.departure_marked_time
        FROM students s
        INNER JOIN classes c ON s.class_id = c.id
        INNER JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
        WHERE ar.departure_method = 'bus' AND s.assigned_bus_id = ?
        ORDER BY ar.departure_marked_time DESC, s.name
      `, [date, userId]);
      
      socket.emit('departure-students-data', {
        success: true,
        data: students
      });
      
    } catch (error) {
      console.error('获取司机离园名单失败:', error);
      socket.emit('departure-students-data', {
        success: false,
        message: '获取离园名单失败'
      });
    }
  });
  
  // 断开连接
   socket.on('disconnect', () => {
     console.log('👋 客户端断开连接:', socket.id);
   });
 });

// 启动服务器
async function startServer() {
  try {
    await testDatabaseConnection();
    
    // 启动自动异常处理定时任务（每30秒检查一次）
    setInterval(autoMarkAbsentAsAbnormal, 30 * 1000);
    
    // 启动定期状态广播任务（每30秒广播一次）
    setInterval(broadcastStudentStatusUpdate, 30 * 1000);
    
    // 每天凌晨1点自动生成当日考勤记录
    cron.schedule('0 1 * * *', () => {
      console.log('🕐 定时任务：开始生成当日考勤记录');
      generateDailyAttendanceRecords();
    }, {
      timezone: 'Asia/Shanghai'
    });
    
    // 服务器启动时延迟10秒执行一次
    setTimeout(autoMarkAbsentAsAbnormal, 10000);
    
    httpServer.listen(PORT, () => {
      console.log(`🚀 服务器运行在 http://localhost:${PORT}`);
      console.log(`📚 API文档: http://localhost:${PORT}/api`);
      console.log('🔄 环境:', process.env.NODE_ENV || 'development');
      console.log('🔌 WebSocket服务已启动');
      console.log('⏰ 自动异常处理定时任务已启动（每30秒检查一次）');
      console.log('📡 学生状态定期广播任务已启动（每30秒广播一次）');
      console.log('🕐 考勤记录自动生成定时任务已启动（每天凌晨1点）');
    });
  } catch (error) {
    console.error('启动服务器失败:', error);
    process.exit(1);
  }
}

startServer();

export default app;
